package management;

import java.sql.Connection;
import java.util.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import org.newdawn.slick.Image;
import org.newdawn.slick.SlickException;

public class DBAccess {

    private Connection connection;

    public DBAccess() {
        enregistrarJDBC();
        establirConnexio();
    }

    /** Enregistra el driver JDBC pel RDBMS. */
    private void enregistrarJDBC() {
        try {

            final String stringDriver = "org.postgresql.Driver";
            Class.forName(stringDriver).newInstance();
        } catch (final Exception e) {
            JOptionPane.showMessageDialog(null, "Impossible enregistrar el driver JDBC. La raó és: " + e.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
        }
    }

    /** Estableix la connexió al RDBMS. */
    private void establirConnexio() {
        try {
            final String url = "jdbc:postgresql://localhost:5432/widasKeeper";
            final String username = "postgres";
            final String password = "jupiter";
            connection = DriverManager.getConnection(url, username, password);
        } catch (final SQLException sqlex) {
            JOptionPane.showMessageDialog(null, "Impossible establir la connexió amb el RDBMS. La raó és: " + sqlex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
        }
    }

    public ResultSet carregarPartides() {
        try {
            final String queryString = "select idGame,savezone,pj1exp,pj2exp,pj3exp,pj4exp,savedate from savedGame;";
            final PreparedStatement ps = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            final ResultSet resultSet = ps.executeQuery();
            return resultSet;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + ex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
            return null;
        }

    }

    public void GuardarPartidaNova(int zona, int expj1, int expj2, int expj3, int expj4) {
        try {
            String path = "";
            final String queryString = "insert into savedgame (savezone,pj1exp,pj2exp,pj3exp,pj4exp,savedate) VALUES (?,?,?,?,?,?)";
            final PreparedStatement ps = connection.prepareStatement(queryString);
            ps.setInt(1, zona); // Substituir el primer ? pel valor
            ps.setInt(2, expj1); // Substituir el segon ? pel valor
            ps.setInt(3, expj2); // Substituir el tercer ? pel valor
            ps.setInt(4, expj3); // Substituir el cuart ? pel valor
            ps.setInt(5, expj4); // Substituir el cinque ? pel valor
            ps.setString(6, new Date().toString()); // Substituir el sise ? pel valor
            final ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                path = resultSet.getString("map_path");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void ActualitzarPartida(int zona, int expj1, int expj2, int expj3, int expj4, int idPartida) {
        try {
            String path = "";
            final String queryString = "update savedgame SET savezone = ?,pj1exp = ?,pj2exp = ?,pj3exp = ?,pj4exp = ?,savedate = ? where idgame = ?";
            final PreparedStatement ps = connection.prepareStatement(queryString);
            ps.setInt(1, zona); // Substituir el primer ? pel valor
            ps.setInt(2, expj1); // Substituir el segon ? pel valor
            ps.setInt(3, expj2); // Substituir el tercer ? pel valor
            ps.setInt(4, expj3); // Substituir el cuart ? pel valor
            ps.setInt(5, expj4); // Substituir el cinque ? pel valor
            ps.setString(6, new Date().toString()); // Substituir el sise ? pel valor
            ps.setInt(7, idPartida);
            final ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                path = resultSet.getString("map_path");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //retorna la ruta d'un mapa
    public String getMapPath(int zone) {
        String path = "";
        final String queryString = "select map_path from maps where zone = ?";
        try {
            final PreparedStatement ps = connection.prepareStatement(queryString);
            ps.setInt(1, zone);  // Substituir el primer ? pel valor

            final ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                path = resultSet.getString("map_path");
            }

        } catch (final SQLException sqlex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + sqlex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
        }
        return path;
    }

    public ResultSet heroIniValues(String nomPj) {
        try {
            final String queryString = "select name,life,energy,jump,speed,xleft,xright,yup,ybottom,"
                    + "axleft,axright,ayup,aybottom,attacktime,shoottime,marginup,margindown,"
                    + "paredsaltable,doblesaltable,rompepared,transformable "
                    + "from characters "
                    + "where name like ?";

            final PreparedStatement ps = connection.prepareStatement(queryString);
            ps.setString(1, nomPj); // Substituir el primer ? pel valor
            final ResultSet resultSet = ps.executeQuery();
            return resultSet;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + ex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
            return null;
        }
    }

    public ResultSet EnemyIniValues(String nomEnemy) {
        try {
            final String queryString = "select name,life,speed,xleft,xright,yup,ybottom,"
                    + "axleft,axright,ayup,aybottom,attacktime,shoottime,marginup,margindown,"
                    + "distanciaseguimiento,distanciadisparo,distanciaparada, experiencia "
                    + "from characters "
                    + "where name like ?";


            final PreparedStatement ps = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setString(1, nomEnemy); // Substituir el primer ? pel valor
            final ResultSet resultSet = ps.executeQuery();

            return resultSet;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + ex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
            return null;
        }
    }

    public ResultSet CharAnimations(String nomCharacter) {
        try {
            final String queryString = "SELECT nombre,accion,animation_index,frametime,img_path FROM animations "
                    + "where nombre like ? "
                    + "ORDER BY accion,animation_index";



            final PreparedStatement ps = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setString(1, nomCharacter); // Substituir el primer ? pel valor
            final ResultSet resultSet = ps.executeQuery();
            return resultSet;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + ex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
            return null;
        }
    }

    public Image recuperarMenuResource(String tipus) {
        String path = "";
        Image img;
        try {

            final String queryString = "select resource_path from menu_resources where nomresource like ?";
            final PreparedStatement ps = connection.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setString(1, tipus); // Substituir el primer ? pel valor
            final ResultSet resultSet = ps.executeQuery();

            //recorro el rs y genero la imagen
            while (resultSet.next()) {
                path = resultSet.getString(1);

            }

            img = new Image(path);

            return img;
        } catch (SlickException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + ex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
            return null;
        }
    }

    /** Tanca la connexió al RDBMS. */
    public void tancarConnexio() {
        try {
            connection.close();
        } catch (final SQLException sqlex) {
            JOptionPane.showMessageDialog(null, "S'ha aixecat una excepció. La raó és: " + sqlex.getMessage(), "Atenció", JOptionPane.INFORMATION_MESSAGE);
        }
    }
}
